package cn.hwua.dao.impl;

import cn.hwua.dao.CompanyDao;
import cn.hwua.entity.Company;
import cn.hwua.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class CompanyDaoImpl implements CompanyDao {
    Connection conn=DBUtil.getConnection();
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(Company obj) {
        String sql="INSERT INTO  tb_company(company_id,company_name,company_tel,company_postcode,company_address,company_fax,company_addtime,company_img) VALUES (?,?,?,?,?,?,?,?)";
        Object[] paras={obj.getCompany_id(),obj.getCompany_name(),obj.getCompany_tel(),obj.getCompany_postcode(),
        obj.getCompany_address(),obj.getCompany_fax(),obj.getCompany_addtime(),obj.getCompany_img()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Company obj) {
        String sql="UPDATE  tb_company SET  company_name=?,company_tel=?,company_postcode=?,company_address=?,company_fax=?,company_addtime=?,company_img=? WHERE company_id=? ";
        Object[] paras={obj.getCompany_name(),obj.getCompany_tel(),obj.getCompany_postcode(), obj.getCompany_address(),
                obj.getCompany_fax(),obj.getCompany_addtime(),obj.getCompany_img(),obj.getCompany_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Company obj) {
        String sql="DELETE FROM tb_company WHERE company_id  = ? ";
        Object[] paras={obj.getCompany_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Company findById(Object id) {
        String sql="SELECT * FROM tb_company where company_id  = ?";
        Object[] paras={id};
        try {
            Company company=qr.query(conn,sql,paras,new BeanHandler<Company>(Company.class));
            return company;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Company> findAll() {
        String sql="SELECT * FROM tb_company";
        Object[] paras={};
        try {
            List<Company> companyList=qr.query(conn,sql,paras,new BeanListHandler<Company>(Company.class));
            return companyList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Company> findSearch(String keyword) {
        String sql="SELECT * FROM tb_company where company_name like concat('%',concat(?,'%'))";
        Object[] paras={keyword};
        try {
            List<Company> companyList=qr.query(conn,sql,paras,new BeanListHandler<Company>(Company.class));
            return companyList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Company> findPage(int page, int pagesize) {
        String sql="SELECT * FROM tb_company limit ?,?";
        Object[] paras={(page-1)*pagesize,pagesize};
        try {
            List<Company> companyList=qr.query(conn,sql,paras,new BeanListHandler<Company>(Company.class));
            return companyList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_company";
        Object[] paras={};
        try {
            long nums=qr.query(conn,sql,paras,new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
